﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    [Serializable]
    public sealed class ExpenseRateTypeDAL : IExpenseRateTypeDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public int Insert(ExpenseRateInfo expRateTypeInfo)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@name", expRateTypeInfo.Name);
                dbManager.AddParameters(1, "@exp", expRateTypeInfo.Expense.Sequence);
                dbManager.AddParameters(2, "@jobsite", expRateTypeInfo.JobSite);
                dbManager.AddParameters(3, "@type", expRateTypeInfo.Type_Sequence);
                dbManager.AddParameters(4, "@user", expRateTypeInfo.User.CreatedBy);
                dbManager.AddParameters(5, "@status", SystemInfo._ActiveRecordStatus);

                return dbManager.ExecuteNonQuery(CommandType.Text, "INSERT INTO BGM_MAS_EXPENSE_RATE(BGM_EXP_RATE_NAME, BGM_EXP_SEQ, JOB_SITE_SEQ, BGM_EXP_RATE_TYPE_SEQ, RECORD_STATUS, CREATED_BY, UPDATED_BY)"
                                    + " VALUES (@name, @exp, @jobsite, @type, @status, @user, @user)");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Update(ExpenseRateInfo expRateTypeInfo)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@seq", expRateTypeInfo.Sequence);
                dbManager.AddParameters(1, "@name", expRateTypeInfo.Name);
                dbManager.AddParameters(2, "@jobsite", expRateTypeInfo.JobSite);
                dbManager.AddParameters(3, "@type", expRateTypeInfo.Type_Sequence);
                dbManager.AddParameters(4, "@exp", expRateTypeInfo.Expense.Sequence);
                dbManager.AddParameters(5, "@status", SystemInfo._ActiveRecordStatus);

                strSQL = "UPDATE BGM_MAS_EXPENSE_RATE"
                + " SET BGM_EXP_RATE_NAME = @name"
                    + ", JOB_SITE_SEQ = @jobsite"
                    + ", BGM_EXP_RATE_TYPE_SEQ = @type"
                    + ", BGM_EXP_SEQ = @exp"
                    + ", RECORD_STATUS = @status"
                + " WHERE BGM_EXP_RATE_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Delete(int seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                return dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE BGM_MAS_EXPENSE_RATE SET RECORD_STATUS = @status WHERE BGM_EXP_RATE_SEQ = @seq");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<ExpenseRateInfo> Search(ExpenseRateInfo expRateTypeInfo)
        {
            List<ExpenseRateInfo> expRateType = new List<ExpenseRateInfo>();
            string strSQL;
            int exp = 0;

            if (expRateTypeInfo.Expense != null)
                exp = expRateTypeInfo.Expense.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@seq", expRateTypeInfo.Sequence);
                dbManager.AddParameters(1, "@name", expRateTypeInfo.Name);
                dbManager.AddParameters(2, "@exp", exp);
                dbManager.AddParameters(3, "@jobsite", expRateTypeInfo.JobSite);
                dbManager.AddParameters(4, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT r.BGM_EXP_RATE_SEQ, r.BGM_EXP_RATE_NAME, rt.BGM_EXP_RATE_TYPE_SEQ, rt.BGM_EXP_RATE_TYPE_NAME"
                    + ", r.BGM_EXP_SEQ, r.RECORD_STATUS, r.JOB_SITE_SEQ, r.UPDATED_BY"
                    + " FROM BGM_MAS_EXPENSE_RATE r"
                        + " INNER JOIN BGM_MAS_EXPENSE_RATE_TYPE rt ON r.BGM_EXP_RATE_TYPE_SEQ = rt.BGM_EXP_RATE_TYPE_SEQ"
                    + " WHERE r.RECORD_STATUS not like @status"
                        + ((expRateTypeInfo.Sequence > 0) ? " AND r.BGM_EXP_RATE_SEQ = @seq" : "")
                        + ((expRateTypeInfo.Name.Length > 0) ? " AND r.BGM_EXP_RATE_NAME = @code" : "")
                        + ((exp > 0) ? " AND r.BGM_EXP_SEQ = @exp" : "")
                        + ((expRateTypeInfo.JobSite.Length > 0) ? " AND r.JOB_SITE_SEQ = @descr" : "");

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    ExpenseRateInfo expRateTypes = new ExpenseRateInfo();
                    expRateTypes.Sequence = Convert.ToInt32(dr["BGM_EXP_RATE_SEQ"]);
                    expRateTypes.Name = Convert.ToString(dr["BGM_EXP_RATE_NAME"]);
                    expRateTypes.JobSite = Convert.ToString(dr["JOB_SITE_SEQ"]);
                }

                //while (rdr.Read())
                //{
                //    // ------------------------------- Expense DAL --------------------------------------

                //    ExpenseDAL expCtrl = new ExpenseDAL();

                //    IList<ExpenseInfo> expList = expCtrl.Search(new ExpenseInfo(rdr.GetInt32(4), "", "", "", "", new ExpenseInfo(0), null, null, null, null, null, null));

                //    // --------------------------------------------------------------------------------------

                //    ExpenseRateInfo expRateTypeRecord = new ExpenseRateInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2), rdr.GetString(3), rdr.GetString(5), rdr.GetString(6), expList[0], new UserRecordInfo(1, rdr.GetInt32(7)));
                //    expRateType.Add(expRateTypeRecord);
                //}

                return expRateType;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<ExpenseRateInfo> GetRateType()
        {
            List<ExpenseRateInfo> expRateType = new List<ExpenseRateInfo>();
            string strSQL;

            try
            {
                dbManager.Open();

                strSQL = "SELECT BGM_EXP_RATE_TYPE_SEQ, BGM_EXP_RATE_TYPE_NAME"
                    + " FROM BGM_MAS_EXPENSE_RATE_TYPE";

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    ExpenseRateInfo expRateTypes = new ExpenseRateInfo();
                    expRateTypes.Sequence = Convert.ToInt32(dr["BGM_EXP_RATE_SEQ"]);
                    expRateTypes.Name = Convert.ToString(dr["BGM_EXP_RATE_TYPE_NAME"]);
                }

                //while (rdr.Read())
                //{
                //    ExpenseRateInfo expRateTypeRecord = new ExpenseRateInfo(rdr.GetInt32(0), rdr.GetString(1));
                //    expRateType.Add(expRateTypeRecord);
                //}

                return expRateType;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
